package com.fsd.core.util;

import java.io.File;
import java.io.FileNotFoundException;
import java.util.ArrayList;
import java.util.List;
import jxl.Cell;
import jxl.CellType;
import jxl.NumberCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.VerticalAlignment;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import com.fsd.core.common.BusinessException;
import org.apache.poi.hssf.usermodel.*;

/**
 * Excel工具
 * @author lumingbao
 */
public class ExcelUtil {
	
	public static List<Object> readExcel(String url, int page, int startRow, int startColumn, int row, int column) throws Exception{
		List<Object> list = new ArrayList<Object>();
		try {
			File file = new File(url); //根据文件名创建一个文件对象
			Workbook wb = Workbook.getWorkbook(file); //从文件流中取得Excel工作区对象
			int size = wb.getSheets().length;//得到Sheet数量
			if(page > size){
				throw new BusinessException("Sheet下标越界！");
			}
			Sheet sheet = wb.getSheet(page - 1);//从工作区中取得页，取得这个对象的时候既可以用名称来获得，也可以用序号。
			if(startRow > sheet.getRows()){
				throw new BusinessException("开始行数越界！");
			}
			if(startColumn > sheet.getColumns()){
				throw new BusinessException("开始列数越界！");
			}
			if((startRow + row)-1 > sheet.getRows()){
				throw new BusinessException("行数超出总行数！");
			}
			if((startColumn + column)-1 > sheet.getColumns()){
				throw new BusinessException("列数超出总列数！");
			}
			
			int nowRow = 0;
			int nowColumn = 0;
			
			for (int i = 0; i < sheet.getRows(); i++) {
				if(i >= (startRow -1)){
					nowRow++;
					List<Object>  columnslist = new ArrayList<Object>();
					for (int j = 0; j < sheet.getColumns(); j++) {
						if(j >= (startColumn-1)){
							nowColumn++;
							Cell cell = sheet.getCell(j,i);
							String text = cell.getContents();
							if(cell.getType() == CellType.NUMBER){
								NumberCell numberCell = (NumberCell) cell;
								double value =numberCell.getValue();
								text = value + "";
							}
							columnslist.add(cell.getContents());
						}
						if(nowColumn == column){
							break;
						}
					}
					list.add(columnslist);
				}
				if(nowRow == row){
					break;
				}
				nowColumn = 0;
			}
			
		} catch(FileNotFoundException e){
			throw new BusinessException("系统找不到指定的文件！");
		} catch(BiffException e){
			throw new BusinessException("文件类型不匹配！");
		}catch (Exception e) {
			throw e;
		}
		return list;
	}
	
	/**
	 * 生成Excel文件
	 * @param list
	 * @param order
	 * @param url
	 * @param sheetName
	 * @throws Exception
	 */
	public void createExcel(List<Object> list, String[] order, String url, String sheetName) throws Exception{
		WritableWorkbook book = Workbook.createWorkbook(new File(url)); //创建可写入的 Excel 工作薄
		WritableSheet sheet = book.createSheet(sheetName, 0); // 创建 Excel 工作表
		
		sheet.mergeCells(0,0,4,2); //a:最左上的列，b最左上的行，c最右下的列，d最右下的行
		
		WritableFont font1 = new WritableFont(WritableFont.ARIAL,8); //字形
		WritableCellFormat format = new WritableCellFormat();
		format.setFont(font1);
		format.setAlignment(Alignment.CENTRE);
		format.setVerticalAlignment(VerticalAlignment.CENTRE);
		Label c10 = new Label(0, 0, "测试", format);
		Label c11 = new Label(0, 3, "测试1");
		sheet.addCell(c10);
		sheet.addCell(c11);
		
		for (int i = 0; i < 10; i++) {
			for (int j = 0; j < 5; j++) {
				Label ca1 = new Label(j, i+2+1, "测试"+i);
				sheet.addCell(ca1);
			}
		}
		
		book.write();
		book.close();
	}


	public static HSSFWorkbook getHSSFWorkbook(String sheetName, String []title, String [][]values, HSSFWorkbook wb){
		// 第一步，创建一个webbook，对应一个Excel文件
		if(wb == null){
			wb = new HSSFWorkbook();
		}
		// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
		HSSFSheet sheet = wb.createSheet(sheetName);
		// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
		HSSFRow row = sheet.createRow(0);
		// 第四步，创建单元格，并设置值表头 设置表头居中
		HSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

		HSSFCell cell = null;
		//创建标题
		for(int i=0;i<title.length;i++){
			cell = row.createCell(i);
			cell.setCellValue(title[i]);
			cell.setCellStyle(style);
		}
		//创建内容
		for(int i=0;i<values.length;i++){
			row = sheet.createRow(i + 1);
			for(int j=0;j<values[i].length;j++){
				row.createCell(j).setCellValue(values[i][j]);
			}
		}

		return wb;
	}

	public static void main(String[] args) {
		try {
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
